-- EXEC SP_TRANSACTION_REPORT_SITIME 0,'02/15/2011', '02/23/2011', '0','UTAC',0,'Transaction'
ALTER PROC SP_TRANSACTION_REPORT_SITIME( @BASE_PRODUCT_ID INT, 
  @FROM_DATE DATETIME, 
  @TO_DATE DATETIME, 
  @PROCESS_TYPE VARCHAR(50), 
  @VENDOR_NAME VARCHAR(50), 
  @PROCESSSTEP_ID INT,
  @REPORT_TYPE   VARCHAR(20) )
AS
BEGIN

	DECLARE @QUARTER_NO		INT
	DECLARE @YEAR		INT

	SELECT @YEAR = CC.CC_YEAR, @QUARTER_NO = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR CQC, COMPANY_CALENDAR CC
		WHERE CQC.CQC_YEARID = CC.CC_YEARID 
		AND @TO_DATE >= CQC_START_DATE 
		AND @TO_DATE <= CQC_END_DATE

	DECLARE @RESULT_STRING NVARCHAR(4000)

	CREATE TABLE #RESULTS_TABLE  ( ProcessStep_Id INT, Transaction_Type VARCHAR(50), VENDOR VARCHAR(50), Part_Number VARCHAR(50), LOT_NUMBER VARCHAR(50), Quantity INT, TRAN_DATE DATETIME, TO_Location VARCHAR(50), TO_ACCOUNT VARCHAR(50), FROM_Location VARCHAR(50), FROM_ACCOUNT VARCHAR(50),TO_Amount NUMERIC(18,4), FROM_Amount NUMERIC(18,4) )

	-- ASSEMBLY_IN
	INSERT INTO #RESULTS_TABLE SELECT Lt.ProcessStep_ID, 'Assembly In', L.L_Vendor_Name, P.Part_Number, LT.Lot_Number, 
	ISNULL( (SELECT MAX(QTY_IN) FROM Lot_Table_History WHERE Lot_ID = Lt.Lot_ID ), LT.Qty_In), LT.DATE_IN, 'Assembly', 
	(SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'Assembly'), 'Die Bank', (SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'Die Bank'),
	(SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id AND YEAR(FIN_YEAR) = @YEAR ), 

     (SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id AND YEAR(FIN_YEAR) = @YEAR ) 
	FROM LOT_TABLE LT, Location L, Product P, Process_Step PS 
	WHERE Lt.Location_Id = L.Location_ID AND Lt.ProcessStep_Id = P.ProcessStep_Id AND Lt.ProcessStep_Id = PS.ProcessStep_ID 
	AND PS.PS_Type = 'ASSEMBLY' AND Lt.Date_IN >= @FROM_DATE AND Lt.Date_IN <= @TO_DATE

	-- ASSEMBLY_OUT
	INSERT INTO #RESULTS_TABLE SELECT Lt.ProcessStep_ID, 'Assembly Out', L.L_Vendor_Name, P.Part_Number, LT.Lot_Number, 
	Ship_Qty, SL.SHIP_DATE, 'Pre Test', 
	(SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'Pre Test'), SL.SL_Next_Process, (SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'Assembly'), 
	(SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = (SELECT NEXT_PROCESSSTEP_ID FROM Process_Step_Traverse_Table WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id ) AND YEAR(FIN_YEAR) = @YEAR ), 

     (SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = (SELECT NEXT_PROCESSSTEP_ID FROM Process_Step_Traverse_Table WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id ) AND YEAR(FIN_YEAR) = @YEAR )
	FROM
	Shipped_Lot SL, LOT_TABLE LT, Location L, Product P, Process_Step PS WHERE SL.Lot_ID = Lt.Lot_ID AND Lt.Location_Id = L.Location_ID AND Lt.ProcessStep_Id = P.ProcessStep_Id AND Lt.ProcessStep_Id = PS.ProcessStep_ID 
	AND PS.PS_Type = 'ASSEMBLY' AND SL.Ship_Date >= @FROM_DATE AND SL.Ship_Date <= @TO_DATE

	-- PRETEST_IN
	INSERT INTO #RESULTS_TABLE SELECT Lt.ProcessStep_ID, 'Pre Test In', L.L_Vendor_Name, P.Part_Number, LT.Lot_Number, 
	ISNULL( (SELECT MAX(QTY_IN) FROM Lot_Table_History WHERE Lot_ID = Lt.Lot_ID ), LT.Qty_In), LT.DATE_IN, 'Pre Test', 
	(SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'Pre Test'), 'Assembly', (SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'Assembly'), 
	(SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id AND YEAR(FIN_YEAR) = @YEAR ), 

     (SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id AND YEAR(FIN_YEAR) = @YEAR )
	FROM
	LOT_TABLE LT, Location L, Product P, Process_Step PS WHERE Lt.Location_Id = L.Location_ID AND Lt.ProcessStep_Id = P.ProcessStep_Id AND Lt.ProcessStep_Id = PS.ProcessStep_ID 
	AND PS.PS_Type = 'Pre Test' AND Lt.Date_IN >= @FROM_DATE AND Lt.Date_IN <= @TO_DATE

	-- PRETEST_OUT
	INSERT INTO #RESULTS_TABLE SELECT Lt.ProcessStep_ID, 'Pre Test Out', L.L_Vendor_Name, P.Part_Number, LT.Lot_Number, 
	Ship_Qty, SL.SHIP_DATE, SL.SL_Next_Process, 
	(SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = SL.SL_Next_Process),  'Pre Test', (SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'Pre Test'),
	(SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id AND YEAR(FIN_YEAR) = @YEAR ), 

     (SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id AND YEAR(FIN_YEAR) = @YEAR )
	FROM
	Shipped_Lot SL, LOT_TABLE LT, Location L, Product P, Process_Step PS WHERE SL.Lot_ID = Lt.Lot_ID AND Lt.Location_Id = L.Location_ID AND Lt.ProcessStep_Id = P.ProcessStep_Id AND Lt.ProcessStep_Id = PS.ProcessStep_ID 
	AND PS.PS_Type = 'Pre Test' AND SL.Ship_Date >= @FROM_DATE AND SL.Ship_Date <= @TO_DATE

	-- FT_IN
	INSERT INTO #RESULTS_TABLE SELECT Lt.ProcessStep_ID, 'FT In', L.L_Vendor_Name, P.Part_Number, LT.Lot_Number, 
	ISNULL( (SELECT MAX(QTY_IN) FROM Lot_Table_History WHERE Lot_ID = Lt.Lot_ID ), LT.Qty_In), LT.DATE_IN, 'FT', 
	(SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'FT'), 'Pre Test', (SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'Pre Test'),
	(SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id AND YEAR(FIN_YEAR) = @YEAR ), 

     (SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id AND YEAR(FIN_YEAR) = @YEAR )
	 FROM
	LOT_TABLE LT, Location L, Product P, Process_Step PS WHERE Lt.Location_Id = L.Location_ID AND Lt.ProcessStep_Id = P.ProcessStep_Id AND Lt.ProcessStep_Id = PS.ProcessStep_ID 
	AND PS.PS_Type = 'FT' AND Lt.Date_IN >= @FROM_DATE AND Lt.Date_IN <= @TO_DATE

	-- FT_OUT
	INSERT INTO #RESULTS_TABLE SELECT Lt.ProcessStep_ID, 'FT Out', L.L_Vendor_Name, P.Part_Number, LT.Lot_Number, 
	Ship_Qty, SL.SHIP_DATE, SL.SL_Next_Process, 
	(SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = SL.SL_Next_Process), 'FT', (SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'FT'), 
	(SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = (SELECT NEXT_PROCESSSTEP_ID FROM Process_Step_Traverse_Table WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id ) AND YEAR(FIN_YEAR) = @YEAR ), 

     (SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = (SELECT NEXT_PROCESSSTEP_ID FROM Process_Step_Traverse_Table WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id ) AND YEAR(FIN_YEAR) = @YEAR )
	FROM
	Shipped_Lot SL, LOT_TABLE LT, Location L, Product P, Process_Step PS WHERE SL.Lot_ID = Lt.Lot_ID AND Lt.Location_Id = L.Location_ID AND Lt.ProcessStep_Id = P.ProcessStep_Id AND Lt.ProcessStep_Id = PS.ProcessStep_ID 
	AND PS.PS_Type = 'FT' AND SL.Ship_Date >= @FROM_DATE AND SL.Ship_Date <= @TO_DATE

	-- Tape&Reel_IN
	INSERT INTO #RESULTS_TABLE SELECT Lt.ProcessStep_ID, 'Tape & Reel In', L.L_Vendor_Name, P.Part_Number, LT.Lot_Number, 
	ISNULL( (SELECT MAX(QTY_IN) FROM Lot_Table_History WHERE Lot_ID = Lt.Lot_ID ), LT.Qty_In), LT.DATE_IN,'Tape & Reel', 
	(SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'Tape & Reel'), 'FT', (SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'FT'), 
	(SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id AND YEAR(FIN_YEAR) = @YEAR ), 

     (SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id AND YEAR(FIN_YEAR) = @YEAR )
	 FROM
	LOT_TABLE LT, Location L, Product P, Process_Step PS WHERE Lt.Location_Id = L.Location_ID AND Lt.ProcessStep_Id = P.ProcessStep_Id AND Lt.ProcessStep_Id = PS.ProcessStep_ID 
	AND PS.PS_Type = 'Tape & Reel' AND Lt.Date_IN >= @FROM_DATE AND Lt.Date_IN <= @TO_DATE

	-- TAPE&REEL_OUT
	INSERT INTO #RESULTS_TABLE SELECT Lt.ProcessStep_ID, 'TAPE & REEL Out', L.L_Vendor_Name, P.Part_Number, LT.Lot_Number, 
	Ship_Qty, SL.SHIP_DATE, SL.SL_Next_Process, 
	(SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = SL.SL_Next_Process), 'Tape & Reel', (SELECT Account_number FROM Process_Account_Table WHERE Vendor_ID = L.Vendor_ID AND Process = 'Tape & Reel'), 
	(SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id AND YEAR(FIN_YEAR) = @YEAR ), 

     (SELECT (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			  END) FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = Lt.ProcessStep_Id AND YEAR(FIN_YEAR) = @YEAR )
	FROM
	Shipped_Lot SL, LOT_TABLE LT, Location L, Product P, Process_Step PS WHERE SL.Lot_ID = Lt.Lot_ID AND Lt.Location_Id = L.Location_ID AND Lt.ProcessStep_Id = P.ProcessStep_Id AND Lt.ProcessStep_Id = PS.ProcessStep_ID 
	AND PS.PS_Type = 'Tape & Reel' AND SL.Ship_Date >= @FROM_DATE AND SL.Ship_Date <= @TO_DATE


	SET @RESULT_STRING = 
		'SELECT  VENDOR, Transaction_Type, Part_Number, LOT_NUMBER, Quantity, TRAN_DATE, TO_Location, TO_ACCOUNT, FROM_Location, FROM_ACCOUNT,TO_Amount, FROM_Amount FROM #RESULTS_TABLE WHERE 1 = 1 '

	IF @PROCESS_TYPE <> '0' AND @PROCESS_TYPE IS NOT NULL
	BEGIN
		SET @RESULT_STRING = @RESULT_STRING + ' AND TO_Location = '''+ @PROCESS_TYPE + ''''	
	END
	
	IF @BASE_PRODUCT_ID > 0 
	BEGIN
		SET @RESULT_STRING = @RESULT_STRING + ' AND ProcessStep_Id IN ( SELECT PROCESSSTEP_ID FROM PRODUCT WHERE P_PARENT_PRODUCT_ID IS NOT NULL AND P_PARENT_PRODUCT_ID = '+ CONVERT(VARCHAR, @BASE_PRODUCT_ID) + ')'
	END
	IF @PROCESSSTEP_ID > 0
	BEGIN
		SET @RESULT_STRING = @RESULT_STRING + ' AND ProcessStep_ID = '+ CONVERT(VARCHAR, @PROCESSSTEP_ID)
	END
	IF @VENDOR_NAME <> '0' AND @VENDOR_NAME IS NOT NULL
	BEGIN
		SET @RESULT_STRING = @RESULT_STRING + ' AND VENDOR = '''+ @VENDOR_NAME + ''''
	END
	--PRINT @RESULT_STRING
	EXEC (@RESULT_STRING)	
END